const express = require('express');
const router = express.Router();
const authenticator = require('../middleware/authenticator');
const Excel = require('exceljs');
const multer = require('multer');
const path = require('path');
const fs = require('fs');


// 配置 multer 存储
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, 'uploads/');
    },
    filename: function (req, file, cb) {
        cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
    }
});

const upload = multer({ storage: storage });



function bookRouteModule(db) {

    //得到图书列表  可传参数，当前页码，当前展示数量  默认为1，10
    router.get('/list', async (req, res) => {
        const page = parseInt(req.query.page) || 1;
        const size = parseInt(req.query.size) || 10;
      
        const offset = (page - 1) * size;
      
        try {
          const [totalResult] = await db.query('SELECT COUNT(*) as total FROM books');
      
          // 得到当前图书列表里的总数
          const total = totalResult[0].total;
      
          const [rows] = await db.query('SELECT * FROM books LIMIT ?,?', [offset, size]);
      
          // 转换字段名为驼峰命名法，并且转换时间格式
          const transformedRows = rows.map(row => ({
            author: row.author,
            bookId: row.book_id,
            categoryId: row.category_id,
            createdAt: row.created_at.toISOString().split('T')[0], // 转换时间格式
            forSale: row.is_for_sale,
            outOfPrint: row.is_out_of_print,
            isbn: row.isbn,
            publicationDate: row.publication_date.toISOString().split('T')[0], // 转换时间格式
            quantity: row.quantity,
            stock: row.stock,
            title: row.title
          }));
      
          res.json({
            code: 200,
            message: "ok",
            data: {
              count: total,
              list: transformedRows
            }
          })
      
          console.log(totalResult, "totalResult");
      
        } catch (error) {
          console.log(error, "错误");
          res.status(500).send({ code: 500, msg: '服务器错误,请稍后重试' });
        }
      })

    //添加图书
    router.post('/add', authenticator, async (req, res) => {
        const { Title, Author, ISBN, Publisher, PublishDate, Category, Stock } = req.body;
        const query = 'INSERT INTO books ( Title, Author, ISBN, Publisher, PublishDate, Category, Stock) VALUES ( ?, ?, ?, ?, ?, ?, ?)';
        const values = [Title, Author, ISBN, Publisher, PublishDate, Category, Stock];
        try {
            const [result] = await db.query(query, values); // 返回插入的行数
            console.log(result, "result");
            res.json({
                code: 200,
                message: "图书添加成功！"
            })
        } catch (error) {
            console.log(error, "错误");
            res.status(500).send({ code: 500, msg: '图书添加失败,请稍后重试' });

        }
    })

    router.get("/searchBook", async (req, res) => {
        // console.log(req.query, "req.query");
        // console.log(req.params, "req.params");
        const keyword = req.query.keyword;

        try {
            // 构建SQL查询语句，使用LIKE模糊查询多个字段
            const sql = `
                SELECT book_id, title, author, isbn, publication_date, quantity, 
                       is_out_of_print, is_for_sale, category_id, created_at
                FROM books 
                WHERE title LIKE? OR author LIKE? OR isbn LIKE?
            `;
            const [results] = await db.query(sql, [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`]);

            res.status(200).send({
                code: 200,
                message: '查询成功',
                data: results
            });
        } catch (error) {
            console.error('查询图书信息失败:', error);
            res.status(500).send({
                code: 500,
                message: '服务器错误，查询图书信息失败'
            });
        }
    })

    // 导入cvs文件接口
    router.post('/uploadFile', upload.single('file'), async (req, res) => {
        const { file } = req.files;
        console.log(file, "file");
        const filePath = file.path;

        // 确保文件存在
        if (!fs.existsSync(filePath)) {
            return res.status(500).send('File not found');
        }

        const query = 'LOAD DATA LOCAL INFILE ? INTO TABLE book FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" (Title, Author, ISBN, Publisher, PublishDate, Category, Stock)';
        const values = [filePath];

        connection.query(query, values, (err, results) => {
            if (err) {
                console.error('Error importing data:', err);
                return res.status(500).send('Error importing data');
            }
            console.log(`Inserted ${results.affectedRows} rows`);
            res.send('File uploaded and data imported successfully');
        });

        // 删除上传的文件
        fs.unlinkSync(filePath);
    })

    // 导出cvs文件接口
    router.get('/export', async (req, res) => {
        const workbook = new Excel.Workbook();
        const worksheet = workbook.addWorksheet('Books');

        worksheet.columns = [
            { header: '书名', key: 'title', width: 20 },
            { header: '作者', key: 'author', width: 20 },
            { header: 'ISBN', key: 'isbn', width: 15 },
            { header: '出版社', key: 'publisher', width: 30 },
            { header: '出版日期', key: 'publishDate', width: 20 },
            { header: '类别', key: 'category', width: 15 },
            { header: '库存', key: 'stock', width: 10 }
        ];


        // 导出工作簿
        workbook.xlsx.writeBuffer().then((buffer) => {
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader('Content-Disposition', 'attachment; filename=books.xlsx');
            res.end(buffer);
        }).catch((error) => {
            console.error('Error writing the Excel file: ', error);
            res.status(500).send('Error generating Excel file');
        });
    })

    // 图书详情
    router.get('/info', async (req, res) => {
        const id = req.query.id;
        console.log(id, "id")

        try {
            const sql = "select * from books where book_id = ?";
            const [rows] = await db.query(sql, [id]);
            if (rows.length > 0) {
                res.json({
                    code: 200,
                    data: rows[0],
                    message: 'ok'
                });
            }

        } catch (error) {
            res.json({
                code: 500,
                message: '服务器错误,请稍后重试'
            })
        }
    })

    // 借书
    router.post("/borrowBool", authenticator, async (req, res) => {
        const { userId, bookId } = req.body;
        const sqlInsertBorrow = "INSERT INTO borrow (book_id, user_id, borrow_date) VALUES (?, ?, NOW())";
        const sqlUpdateStock = "UPDATE books SET stock = stock - 1 WHERE book_id = ? AND stock > 0";
      
        try {
          await db.beginTransaction(); // 开启事务
      
          // 查询书籍数量
          const [bookStock] = await db.query("SELECT stock FROM books WHERE book_id = ?", [bookId]);
          const stock = bookStock[0]?.stock;
      
          if (!stock || stock <= 0) {
            await db.rollback();
            return res.json({
              code: 400,
              message: "库存不足"
            });
          }
      
          // 减少库存
          await db.query(sqlUpdateStock, [bookId]);
      
          // 生成借阅信息
          await db.query(sqlInsertBorrow, [bookId, userId]);
      
          await db.commit(); // 提交事务
      
          res.json({
            code: 200,
            message: "借阅成功"
          });
      
        } catch (error) {
          await db.rollback(); // 发生错误，回滚事务
          console.error(error);
          res.json({
            code: 500,
            message: "服务器错误"
          });
        }
      });

    return router;
}

module.exports = bookRouteModule;